﻿using Microsoft.Practices.EnterpriseLibrary.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XXVIII.Entity;

namespace XXVIII.DAL.SqlServerDAL
{
    public sealed partial class SqlServerHelper : IDisposable
    {
        private Database dbContext = null;

        /// <summary>
        /// 记录生成表信息
        /// </summary>
        private static readonly string SYS_TABLE = "SYS_TABLE";

        #region 构造
        private SqlServerHelper() { }
        private static SqlServerHelper _instance;
        public static SqlServerHelper Instance
        {
            get
            {
                return _instance ?? (_instance = new SqlServerHelper()
                {
                    dbContext = SqlServerDatabase.DbContext,
                });
            }
        }

        #endregion

        #region 析构
        ~SqlServerHelper()
        {
            this.Dispose();
        }
        #endregion

        internal bool Add(string tableName, Dictionary<string, string> keyValue)
        {
            var tableProperty = this.GetTableProperty(tableName);
            string keys = string.Empty;
            string values = string.Empty;
            bool isContainPK = false;
            foreach (KeyValuePair<string, string> kv in keyValue)
            {

                var pInfo = tableProperty.Where(p => p.ColumnName == kv.Key).FirstOrDefault();
                if (pInfo != null && pInfo.IsKey == true)
                {
                    isContainPK = true;
                }
                if (pInfo.IsIdentity == true)
                {
                    continue;
                }
                keys += kv.Key + ",";
                values += this.GetValueByColumnType(pInfo.ColumnName, kv.Value) + ",";
            }

            //不包含主键时，创建主键字段
            if (isContainPK == false)
            {
                var pkColumn = tableProperty.Where(p => p.IsKey == true).FirstOrDefault();
                if (pkColumn != null && pkColumn.IsIdentity == false)
                {
                    if (pkColumn.ColumnType == "int")
                    {
                        var pk = this.GetTableMAXByPK(tableName, pkColumn.ColumnName);
                        keys = pkColumn.ColumnName + "," + keys;
                        values = pk + "," + values;
                    }
                    else if (pkColumn.ColumnType == "uniqueidentifier")
                    {
                        keys = pkColumn.ColumnName + "," + keys;
                        values = this.GetValueByColumnType(pkColumn.ColumnName, System.Guid.NewGuid().ToString()) + "," + values;
                    }
                }
            }

            if (!string.IsNullOrEmpty(keys) && !string.IsNullOrEmpty(values))
            {
                int addRowCount = 0;
                keys = keys.TrimEnd(',');
                values = values.TrimEnd(',');
                string sql = " INSERT INTO {0}({1}) VALUES({2}); ";
                sql = string.Format(sql, tableName, keys, values);
                DbCommand command = dbContext.GetSqlStringCommand(sql);
                int.TryParse(dbContext.ExecuteNonQuery(command).ToString(), out addRowCount);
                return addRowCount > 0;
            }
            return false;
        }

        internal DataTable Get(string tableName, List<TableAndOrLikeEntity> andOrLikeList)
        {
            var tableProperty = this.GetTableProperty(tableName);
            string whereSql = string.Empty;
            string orderBySql = string.Empty;
            whereSql = this.TuneUpWhereSql(tableProperty, andOrLikeList);
            orderBySql = this.TuneUpOrderBySql(tableProperty, andOrLikeList);
            string sql = " SELECT * FROM {0} WHERE  1=1  {1}  {2} ";
            sql = string.Format(sql, tableName, whereSql, orderBySql);
            DbCommand resultSqlCommand = dbContext.GetSqlStringCommand(sql);
            using (IDataReader dataReader = dbContext.ExecuteReader(resultSqlCommand))
            {
                var result = DataReaderConvert.ReaderToDatable(dataReader);
                return result;
            }
        }

        internal bool Update(string tableName, Dictionary<string, string> updateKeyValue, List<TableAndOrLikeEntity> andOrLikeList)
        {
            var tableProperty = this.GetTableProperty(tableName);
            string setSql = string.Empty;
            string whereSql = string.Empty;
            foreach (KeyValuePair<string, string> kv in updateKeyValue)
            {
                var exist = tableProperty.Where(p => p.ColumnName == kv.Key).FirstOrDefault();
                if (exist != null)
                {
                    setSql += "{0}={1},";
                    setSql = string.Format(setSql, exist.ColumnName, this.GetValueByColumnType(exist.ColumnName, kv.Value));
                }
            }

            whereSql = this.TuneUpWhereSql(tableProperty, andOrLikeList);

            if (!string.IsNullOrEmpty(setSql))
            {
                int updateRowCount = 0;
                setSql = setSql.TrimEnd(',');
                string sql = " UPDATE {0} SET {1}  WHERE 1=1  {2}";
                sql = string.Format(sql, tableName, setSql, whereSql);
                DbCommand command = dbContext.GetSqlStringCommand(sql);
                int.TryParse(dbContext.ExecuteNonQuery(command).ToString(), out updateRowCount);
                return updateRowCount > 0;
            }
            return false;
        }

        internal bool Delete(string tableName, List<TableAndOrLikeEntity> andOrLikeList)
        {
            var tableProperty = this.GetTableProperty(tableName);
            string whereSql = string.Empty;
            whereSql = this.TuneUpWhereSql(tableProperty, andOrLikeList);
            if (!string.IsNullOrEmpty(whereSql))
            {
                int delRowCount = 0;
                string sql = " DELETE FROM {0} WHERE 1=1  {1}";
                sql = string.Format(sql, tableName, whereSql);
                DbCommand command = dbContext.GetSqlStringCommand(sql);
                int.TryParse(dbContext.ExecuteNonQuery(command).ToString(), out delRowCount);
                return delRowCount > 0;
            }
            return false;
        }

        internal DataTable GetByPage(string tableName, List<TableAndOrLikeEntity> andOrLikeList, ref DBPageBase page)
        {
            var tableProperty = this.GetTableProperty(tableName);
            string whereSql = string.Empty;
            string orderBySql = string.Empty;
            int startRow = page.PageSize * (page.PageIndex - 1);
            int endRow = startRow + page.PageSize;
            whereSql = this.TuneUpWhereSql(tableProperty, andOrLikeList);
            orderBySql = this.TuneUpOrderBySql(tableProperty, andOrLikeList);
            string orderByCoumnName = string.Empty;
            var tempPK = tableProperty.Where(p => p.IsKey == true).FirstOrDefault();
            if (tempPK != null)
            {
                orderByCoumnName = tempPK.ColumnName;
            }
            else if (tableProperty != null && tableProperty.Count > 0)
            {
                orderByCoumnName = tableProperty.FirstOrDefault().ColumnName;
            }
            if (!string.IsNullOrEmpty(orderByCoumnName))
            {
                string resultSql = "SELECT * FROM (";
                resultSql += " SELECT ROW_NUMBER() OVER (ORDER BY " + orderByCoumnName + ") AS RowNumber,* FROM " + tableName + " ";
                resultSql += " )R";
                resultSql += " WHERE R.RowNumber BETWEEN " + (startRow + 1) + " ";
                resultSql += " AND " + endRow + " ";
                resultSql += " " + whereSql;
                resultSql += " " + orderBySql;

                string countSql = "SELECT COUNT(1) FROM " + tableName + "";
                countSql += " WHERE 1=1 ";
                countSql += " " + whereSql;
                DbCommand countCommand = dbContext.GetSqlStringCommand(countSql);
                int count = 0;
                int.TryParse(dbContext.ExecuteScalar(countCommand).ToString(), out count);
                DbCommand resultSqlCommand = dbContext.GetSqlStringCommand(resultSql);
                using (IDataReader dataReader = dbContext.ExecuteReader(resultSqlCommand))
                {
                    var result = DataReaderConvert.ReaderToDatable(dataReader);
                    page.TotalRows = count;
                    return result;
                }
            }
            return null;
        }

        internal bool CreateSignTable(string tableName, List<CreateCloumnEntity> columns)
        {
            string createSql = " CREATE TABLE [{0}]({1});";
            string bodySql = string.Empty;
            string describeSql = string.Empty;
            columns = columns.OrderByDescending(o => o.IsKey).ToList();
            foreach (var item in columns)
            {
                DefaultValueRule.SetCreateSignTable(item);
                string columnSql = " [" + item.ColumnName + "] ";
                if (item.Length > 0 && item.DecimalLength == 0)
                {
                    columnSql += " " + item.ColumnType + "(" + item.Length + ") ";
                }
                else if (item.Length > 0 && item.DecimalLength > 0)
                {
                    columnSql += " " + item.ColumnType + "(" + item.Length + "," + item.DecimalLength + ") ";
                }
                else
                {
                    columnSql += " " + item.ColumnType + " ";
                }

                if (!string.IsNullOrEmpty(item.DefualtValue))
                {
                    columnSql += "  default(" + item.DefualtValue + ") ";
                }
                else if (item.IsIdentity == true)
                {
                    item.IdentitySeed = item.IdentitySeed == 0 ? 1 : item.IdentitySeed;
                    item.IdentityIncrement = item.IdentityIncrement == 0 ? 1 : item.IdentityIncrement;
                    columnSql += "  IDENTITY(" + item.IdentitySeed + "," + item.IdentityIncrement + ") ";
                }

                if (item.IsKey == true)
                {
                    columnSql += " PRIMARY KEY NOT NULL ";
                }
                else
                {
                    if (item.IsNULL == true)
                    {
                        columnSql += " NULL ";
                    }
                    else
                    {
                        columnSql += " NOT NULL ";
                    }
                }
                if (!string.IsNullOrEmpty(item.Describe))
                {
                    describeSql += "EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo'";
                    describeSql += ", @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}' ;";
                    describeSql = string.Format(describeSql, item.Describe, tableName, item.ColumnName);
                }
                columnSql += ",";
                bodySql += columnSql;
            }
            if (!string.IsNullOrEmpty(tableName) && columns != null && columns.Count > 0)
            {
                bodySql = bodySql.TrimEnd(',');
                createSql = string.Format(createSql, tableName, bodySql);
                DbCommand command = dbContext.GetSqlStringCommand(createSql);
                dbContext.ExecuteNonQuery(command);

                //追加说明
                if (!string.IsNullOrEmpty(describeSql))
                {
                    DbCommand dcommand = dbContext.GetSqlStringCommand(describeSql);
                    dbContext.ExecuteNonQuery(dcommand);
                }
                return true;
            }
            return false;
        }

        internal List<TablePropertyEntity> GetTableProperty(string tableName)
        {
            List<TablePropertyEntity> list = new List<TablePropertyEntity>();
            string sql = " select c.name as ColumnName,t.name as ColumnType ";
            sql += "  ,convert(bit,c.IsNullable)  as IsNULL ";
            sql += "  ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (  ";
            sql += "    select name from sysindexes where indid in( ";
            sql += "   select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end)   as IsKey ";
            sql += "  ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as IsIdentity  ";
            sql += "  ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as Length ";
            sql += "   ,ISNULL(CM.text,'') as DefualtValue ";
            sql += "   ,ISNULL(ETP.value,'') AS Describe ";
            sql += "   from syscolumns c ";
            sql += " inner join systypes t on c.xusertype = t.xusertype ";
            sql += " left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description'  ";
            sql += " left join syscomments CM on c.cdefault=CM.id  ";
            sql += " where c.id = object_id('{0}') ";

            sql = string.Format(sql, tableName);
            DbCommand resultSqlCommand = dbContext.GetSqlStringCommand(sql);
            using (IDataReader dataReader = dbContext.ExecuteReader(resultSqlCommand))
            {
                list = DataReaderConvert.ReaderToList<TablePropertyEntity>(dataReader);
            }
            return list;
        }

        internal bool DropSignTable(string tableName)
        {
            string sql = "DROP TABLE {0} ";
            sql = string.Format(sql, tableName);
            DbCommand command = dbContext.GetSqlStringCommand(sql);
            dbContext.ExecuteNonQuery(command);
            return true;
        }

        internal bool CheckTableExist(string tableName)
        {
            string sql = "SELECT COUNT(1) FROM information_schema.TABLES WHERE TABLE_NAME ='{0}'";
            sql = string.Format(sql, tableName);
            DbCommand cmd = dbContext.GetSqlStringCommand(sql);
            if (dbContext.ExecuteScalar(cmd) != null)
            {
                return Convert.ToInt32(dbContext.ExecuteScalar(cmd)) > 0;
            }
            else
            {
                return false;
            }
        }

        internal DataTable Get(string sql)
        {
            DbCommand resultSqlCommand = dbContext.GetSqlStringCommand(sql);
            using (IDataReader dataReader = dbContext.ExecuteReader(resultSqlCommand))
            {
                var result = DataReaderConvert.ReaderToDatable(dataReader);
                return result;
            }
        }

        internal bool ExecuteSql(string sql)
        {
            int executeRowCount = 0;
            DbCommand command = dbContext.GetSqlStringCommand(sql);
            int.TryParse(dbContext.ExecuteNonQuery(command).ToString(), out executeRowCount);
            return executeRowCount > 0;
        }

        /// <summary>
        /// 记录生成表信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="customJson"></param>
        /// <param name="isJoin"></param>
        public void RecordToTable(string tableName, string customJson, bool isJoin = false)
        {
            var existSYSTable = this.CheckTableExist(SYS_TABLE);
            var createState = false;
            if (!existSYSTable)
            {
                List<CreateCloumnEntity> createList = new List<CreateCloumnEntity>();
                CreateCloumnEntity id = new CreateCloumnEntity();
                id.ColumnName = "ID";
                id.ColumnType = "int";
                id.Length = 0;
                id.IsKey = true;
                id.IsIdentity = true;
                id.IdentityIncrement = 1;
                id.IdentitySeed = 1;
                id.Describe = "主键";
                createList.Add(id);

                CreateCloumnEntity tableNameCloumn = new CreateCloumnEntity();
                tableNameCloumn.ColumnName = "TableName";
                tableNameCloumn.ColumnType = "nvarchar";
                tableNameCloumn.Length = 0;
                tableNameCloumn.IsNULL = false;
                tableNameCloumn.Describe = "表名";
                createList.Add(tableNameCloumn);

                CreateCloumnEntity customJsonCloumn = new CreateCloumnEntity();
                customJsonCloumn.ColumnName = "CustomJson";
                customJsonCloumn.ColumnType = "nvarchar";
                customJsonCloumn.Length = 2048;
                customJsonCloumn.IsNULL = false;
                customJsonCloumn.Describe = "表名";
                createList.Add(customJsonCloumn);

                CreateCloumnEntity isJoinCloumn = new CreateCloumnEntity();
                isJoinCloumn.ColumnName = "IsJoin";
                isJoinCloumn.ColumnType = "bit";
                isJoinCloumn.Length = 0;
                isJoinCloumn.IsNULL = false;
                isJoinCloumn.Describe = "是否联合";
                createList.Add(isJoinCloumn);

                CreateCloumnEntity createrTime = new CreateCloumnEntity();
                createrTime.ColumnName = "AddTime";
                createrTime.ColumnType = "datetime";
                createrTime.Length = 0;
                createrTime.IsNULL = false;
                createrTime.Describe = "创建时间";
                createList.Add(createrTime);
                createState = this.CreateSignTable(SYS_TABLE, createList);
            }
            if (existSYSTable || createState)
            {
                var allWhere = new List<TableAndOrLikeEntity>();
                TableAndOrLikeEntity where = new TableAndOrLikeEntity();
                where.ColumnName = "TableName";
                where.Value = tableName;
                where.AndOrLikeType = 1;
                allWhere.Add(where);
                var existInfo = this.Get(tableName, allWhere);
                if (existInfo == null || existInfo.Rows.Count == 0)
                {
                    Dictionary<string, string> keyValue = new Dictionary<string, string>();
                    keyValue.Add("TableName", tableName);
                    keyValue.Add("CustomJson", customJson);
                    keyValue.Add("IsJoin", isJoin == true ? "1" : "0");
                    keyValue.Add("AddTime", DateTime.Now.ToString());
                    var addRow = this.Add(SYS_TABLE, keyValue);
                }
            }
        }

        /// <summary>
        /// 清理记录生成表信息
        /// </summary>
        public void UnRecordToTable(string tableName)
        {
            var existSYSTable = this.CheckTableExist(SYS_TABLE);
            if (existSYSTable)
            {
                var allWhere = new List<TableAndOrLikeEntity>();
                TableAndOrLikeEntity where = new TableAndOrLikeEntity();
                where.ColumnName = "TableName";
                where.Value = tableName;
                where.AndOrLikeType = 1;
                allWhere.Add(where);
                var existInfo = this.Get(SYS_TABLE, allWhere);
                if (existInfo != null && existInfo.Rows.Count > 0)
                {
                    var deleteRow = this.Delete(SYS_TABLE, allWhere);
                }
            }
        }
        public void Dispose()
        {
            dbContext = null;
        }
    }
}
